package net.wicp.tams.common.apiext.jdbc;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;

import com.google.protobuf.ProtocolStringList;

import lombok.extern.slf4j.Slf4j;
import net.wicp.tams.common.Result;
import net.wicp.tams.common.apiext.CollectionUtil;
import net.wicp.tams.common.constant.dbType.BinlogType;
import net.wicp.tams.common.constant.dic.YesOrNo;
import net.wicp.tams.common.exception.ExceptAll;
import net.wicp.tams.common.exception.ProjectException;
import net.wicp.tams.common.exception.ProjectExceptionRuntime;

@Slf4j
public abstract class MySqlAssit {
	public static String getColsForQuery(String[] cols) {
		String colsstr = "`" + CollectionUtil.arrayJoin(cols, "`,`") + "`";
		return colsstr;
	}

	/***
	 * 批量更新mysql数据
	 * 
	 * @param conn    连接
	 * @param db      数据库名
	 * @param tb      表名
	 * @param datas   要新增的数据
	 * @param cols    要新增的列名
	 * @param typeAry 类型，用于兼容为空的情况
	 * @return
	 */
	public static Result dataUpset(Connection conn, String db, String tb, Object[][] datas, String[] cols,
			BinlogType[] typeAry) {
		Object[][] priDatas = new Object[datas.length][];
		try {
			String[] tempAry = new String[cols.length];
			for (int i = 0; i < tempAry.length; i++) {
				tempAry[i] = "?";
			}
			String sql = String.format("replace into %s.%s (%s) values (%s)", db, tb, MySqlAssit.getColsForQuery(cols),
					CollectionUtil.arrayJoin(tempAry, ","));
			conn.setAutoCommit(false);
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.clearBatch();
			for (int i = 0; i < datas.length; i++) {
				if (i < datas.length - 1) {
					int indexOf = CollectionUtil.indexOf(priDatas, priDatas[i], i + 1);
					if (indexOf >= 0) {
						continue;
					}
				}
				JdbcAssit.setPreParam(stmt, typeAry, datas[i]);
				stmt.addBatch();
			}
			stmt.executeBatch();
			conn.commit();
			stmt.close();
			return Result.getSuc();
		} catch (Exception e) {
			log.error("批量插入失败", e);
			return new Result(ExceptAll.jdbc_exec_fail);
		}
	}

	public static Result dataUpset(Connection conn, JdbcDatas datas, boolean isUpset) {
		List<JdbcData> datasList = datas.getDatasList();
		Object[][] datasInput = new Object[datasList.size()][];
		for (int i = 0; i < datasList.size(); i++) {
			JdbcData jdbcData = datasList.get(i);
			datasInput[i] = new Object[datas.getColsCount()];
			for (int j = 0; j < datas.getColsCount(); j++) {
				String value = jdbcData.getValueMap().get(datas.getCols(j));
				if (value == null) {
					datasInput[i][j] = value;
				} else {
					BinlogType binlogType = BinlogType.valueOf(datas.getTypeMap().get(datas.getCols(j)));
					Serializable value2 = BinlogType.getValue(binlogType, value);
					datasInput[i][j] = value2;
				}
			}
		}
		Map<String, String> typeMap = datas.getTypeMap();
		ProtocolStringList colsList = datas.getColsList();
		BinlogType[] typeAry = new BinlogType[colsList.size()];
		for (int i = 0; i < typeAry.length; i++) {
			typeAry[i] = BinlogType.valueOf(typeMap.get(colsList.get(i)));
		}
		return dataUpset(conn, datas.getDb(), datas.getTb(), datasInput,
				datas.getColsList().toArray(new String[datas.getColsList().size()]), typeAry);
	}

	public static Result dataDelete(Connection conn, String db, String tb, Object[][] datas, String[] keys) {
		if (ArrayUtils.isEmpty(keys)) {
			keys = getPrimary(conn, db, tb);
		}
		if (!ArrayUtils.isEmpty(keys)) {// 需要删除ID
			StringBuilder builder = new StringBuilder(String.format("delete from %s.%s where 1=1", db, tb));
			for (String key : keys) {
				builder.append(String.format(" and %s=?", key));
			}
			try {
				conn.setAutoCommit(false);
				PreparedStatement stmtDel = conn.prepareStatement(builder.toString());
				for (int i = 0; i < datas.length; i++) {
					JdbcAssit.setPreParam(stmtDel, null, datas[i]);
					stmtDel.addBatch();
				}
				stmtDel.executeBatch();
				conn.commit();
				stmtDel.close();
				return Result.getSuc();
			} catch (SQLException e) {
				log.error("通过主键删除失败", e);
				return new Result(new ProjectException(ExceptAll.jdbc_exec_fail, "通过主键删除失败"));
			}
		} else {
			throw new ProjectExceptionRuntime(ExceptAll.jdbc_exec_fail);
		}
	}

	public static Result dataDelete(Connection conn, JdbcDatas datas) {
		List<JdbcData> datasList = datas.getDatasList();
		Object[][] datasInput = new Object[datasList.size()][];
		for (int i = 0; i < datasList.size(); i++) {
			JdbcData jdbcData = datasList.get(i);
			datasInput[i] = new Object[datas.getKeysCount()];
			for (int j = 0; j < datas.getKeysCount(); j++) {
				String value = jdbcData.getValueMap().get(datas.getKeys(j));
				BinlogType binlogType = BinlogType.valueOf(datas.getTypeMap().get(datas.getKeys(j)));
				Serializable value2 = BinlogType.getValue(binlogType, value);
				datasInput[i][j] = value2;
			}
		}
		return dataDelete(conn, datas.getDb(), datas.getTb(), datasInput,
				datas.getKeysList().toArray(new String[datas.getKeysCount()]));
	}

	public static Result dataChange(Connection conn, JdbcDatas datas) {
		if (datas.getOptType() == OptType.delete) {
			return dataDelete(conn, datas);
		} else {
			return dataUpset(conn, datas, true);
		}
	}

	public static String[] getPrimary(Connection conn, String db, String tb) {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(
					"SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name,table_schema,table_name) WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema=? AND t.table_name=?");
			JdbcAssit.setPreParam(preparedStatement, null, db, tb);
			ResultSet rs2 = preparedStatement.executeQuery();
			if (!rs2.next()) {
				return new String[0];
			}
			List<String> retlist = new ArrayList<>();
			retlist.add(rs2.getString(1));
			while (rs2.next()) {
				retlist.add(rs2.getString(1));
			}
			return retlist.toArray(new String[retlist.size()]);
		} catch (SQLException e) {
			log.error("查主键失败");
			throw new ProjectExceptionRuntime(ExceptAll.jdbc_query_primary);
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
			} catch (Exception e2) {
				log.error("关闭preparedStatement失败", e2);
			}

		}

	}

	/***
	 * retAry[0] 列名 retAry[1] 列类型
	 * 
	 * @param conn
	 * @param db
	 * @param tb
	 * @param isRds
	 * @return
	 */
	public static String[][] getCols(Connection conn, String db, String tb, YesOrNo isRds) {
		try {
			PreparedStatement prepCols = conn.prepareStatement(
					"select   column_name,data_type   from  information_schema.columns  where  table_schema=? and table_name=?");
			List<String> ret = new ArrayList<>();
			List<String> retType = new ArrayList<>();
			JdbcAssit.setPreParam(prepCols, null, db, tb);
			ResultSet rs = prepCols.executeQuery();
			while (rs.next()) {
				ret.add(rs.getString(1));
				retType.add(rs.getString(2));
			}
			rs.close();
			if (YesOrNo.yes == isRds && ArrayUtils.isEmpty(getPrimary(conn, db, tb))) {// 是rds
				ret.add("_rowkey_");
				retType.add("varchar");
			}
			String[][] retAry = new String[2][ret.size()];
			retAry[0] = ret.toArray(new String[ret.size()]);
			retAry[1] = retType.toArray(new String[retType.size()]);
			return retAry;
		} catch (Exception e) {
			log.error("获取cols错误", e);
			throw new RuntimeException("获取cols错误");
		}
	}
}
